package cn.green.core.excel.model.impls;

import cn.green.core.components.field.FieldCheckTypeCom;
import cn.green.core.components.store.field.FieldCheckTypeStore;
import cn.green.core.excel.QExcel;
import cn.green.core.excel.model.Column;
import cn.green.core.model.QField;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import lombok.Data;
import lombok.Setter;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.util.*;

import static cn.green.core.excel.model.impls.SelectorColumn.SPLIT_SYMBOL;

/**
 * todo 需要进一步开发
 * @author wang.yishu
 * @version 1.0
 * @className CascadedColumn
 * @description 级联选择器
 * @date 2025/3/25 09:21
 */
public class CascadedColumn extends Column {

    //该级联选择器的级联字段列表
    @Setter
    private List<CascadedItem> columnCascadedItems;

    protected Map<CascadedOption, List<CascadedOption>> cascadedOptions;
    //该级联选择器的根数据
    protected List<CascadedOption> rootData;

    @Data
    private static class CascadedItem {
        protected int index;
        protected int lookupIndex = -1;
        protected String columnName;
        protected QField field;
    }

    @Data
    public static class CascadedOption {
        protected String value;
        protected String label;

        public String option() {
            return label + SPLIT_SYMBOL + value;
        }

        @Override
        public boolean equals(Object obj) {
            if (obj instanceof CascadedOption) {
                CascadedOption option = (CascadedOption) obj;
                return option.value.equals(value) && option.label.equals(label);
            }
            return false;
        }

        @Override
        public int hashCode() {
            return (label + SPLIT_SYMBOL + value).hashCode();
        }
    }

    /**
     * 构造方法
     * @param index             级联的第一位位置
     * @param fields            级联的字段
     * @param cascadedOptions   级联的下拉数据
     */
    public CascadedColumn(Integer index, QField mainField, List<QField> fields, List<CascadedOption> rootData, Map<CascadedOption, List<CascadedOption>> cascadedOptions) {
        super(index, mainField);
        this.field = mainField;
        this.index = index;
        this.maxRow = 65535;
        this.rootData = rootData;
        this.cascadedOptions = cascadedOptions;
        if (fields != null && !fields.isEmpty()) {
            List<CascadedItem> cascadedItems = new ArrayList<>();
            for (QField field : fields) {
                CascadedItem cascadedItem = new CascadedItem();
                cascadedItem.index = index;
                cascadedItem.field = field;
                cascadedItem.columnName = field.getAliasName();
                List<String> fieldCheck = this.field.getFieldCheck();
                if (fieldCheck != null) {
                    for (String fieldCheckKey : fieldCheck) {
                        FieldCheckTypeCom fieldCheckTypeCom = FieldCheckTypeStore.get(fieldCheckKey);
                        if (fieldCheckTypeCom != null) {
                            if (fieldCheckTypeCom.required()) {
                                cascadedItem.columnName = field.getAliasName() + "*";
                            }
                        }
                    }
                }
                cascadedItems.add(cascadedItem);
            }
            this.columnCascadedItems = cascadedItems;
        }
    }
    /**
     * 构造方法
     * @param index             级联的第一位位置
     * @param fields            级联的字段
     * @param cascadedOptions   级联的下拉数据
     */
    public CascadedColumn(Integer index, QField mainField, List<QField> fields, JSONArray rootData, Map<String, JSONArray> cascadedOptions) {
        super(index, mainField);
        this.field = mainField;
        this.index = index;
        this.maxRow = 65535;
        List<CascadedOption> rootDataList = new ArrayList<>();
        if (rootData != null) {
            for (int i = 0; i < rootData.size(); i++) {
                JSONObject opt = rootData.getJSONObject(i);
                CascadedOption cascadedOption = new CascadedOption();
                cascadedOption.setValue(opt.getString("value"));
                cascadedOption.setLabel(opt.getString("label"));
                rootDataList.add(cascadedOption);
            }
            this.rootData = rootDataList;
        }

        if (cascadedOptions != null && !cascadedOptions.isEmpty()) {
            Set<String> rootOpts = cascadedOptions.keySet();
            Map<CascadedOption, List<CascadedOption>> cascadedOptionsMap = new HashMap<>();
            for (String optKey : rootOpts) {
                if (this.rootData != null) {
                    this.rootData.stream().filter(item -> item.option().equals(optKey)).findFirst().ifPresent(item -> {
                        JSONArray array = cascadedOptions.get(item.option());
                        if (array != null) {
                            List<CascadedOption> dataList = new ArrayList<>();
                            for (int i = 0; i < array.size(); i++) {
                                JSONObject opt = array.getJSONObject(i);
                                CascadedOption cascadedOption = new CascadedOption();
                                cascadedOption.setValue(opt.getString("value"));
                                cascadedOption.setLabel(opt.getString("label"));
                                dataList.add(cascadedOption);
                            }
                            cascadedOptionsMap.put(item, dataList);
                        }
                    });
                }
            }
            this.cascadedOptions = cascadedOptionsMap;
        }
        if (fields != null && !fields.isEmpty()) {
            List<CascadedItem> cascadedItems = new ArrayList<>();
            for (QField field : fields) {
                CascadedItem cascadedItem = new CascadedItem();
                cascadedItem.index = index;
                cascadedItem.field = field;
                cascadedItem.columnName = field.getAliasName();
                List<String> fieldCheck = this.field.getFieldCheck();
                if (fieldCheck != null) {
                    for (String fieldCheckKey : fieldCheck) {
                        FieldCheckTypeCom fieldCheckTypeCom = FieldCheckTypeStore.get(fieldCheckKey);
                        if (fieldCheckTypeCom != null) {
                            if (fieldCheckTypeCom.required()) {
                                cascadedItem.columnName = field.getAliasName() + "*";
                            }
                        }
                    }
                }
                cascadedItems.add(cascadedItem);
            }
            this.columnCascadedItems = cascadedItems;
        }
    }


    @Override
    public List<HSSFCell> generateXlsTitle(HSSFWorkbook workbook, HSSFSheet sheet, HSSFRow row) {
        int offset = 0;
        List<HSSFCell> rtn = new ArrayList<>();
        for (CascadedItem columnCascadedItem : this.columnCascadedItems) {
            HSSFCell cell = row.createCell(this.index + offset);
            columnCascadedItem.setIndex(this.index + offset);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            if (this.xlsTitleCellStyle != null) {
                cell.setCellStyle(this.xlsTitleCellStyle);
            }
            cell.setCellValue(columnCascadedItem.columnName);
            if (offset > 0) {
                columnCascadedItem.lookupIndex = this.index + offset - 1;
            }
            offset++;
            rtn.add(cell);
        }
        //开始建造级联下拉选项
        this.buildCascadedXls(workbook, sheet);
        return rtn;
    }

    @Override
    public List<XSSFCell> generateXlsxTitle(XSSFWorkbook workbook, XSSFSheet sheet, XSSFRow row) {
        int offset = 0;
        List<XSSFCell> rtn = new ArrayList<>();
        for (CascadedItem columnCascadedItem : this.columnCascadedItems) {
            XSSFCell cell = row.createCell(this.index + offset);
            columnCascadedItem.setIndex(this.index + offset);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            if (this.xlsTitleCellStyle != null) {
                cell.setCellStyle(this.xlsTitleCellStyle);
            }
            cell.setCellValue(columnCascadedItem.columnName);
            if (offset > 0) {
                columnCascadedItem.lookupIndex = this.index + offset - 1;
            }
            offset++;
            rtn.add(cell);
        }
        //开始建造级联下拉选项
        this.buildCascadedXlsx(workbook, sheet);
        return rtn;
    }



    @Override
    public List<HSSFCell> generateXlsCell(HSSFWorkbook workbook, HSSFSheet sheet, HSSFRow row, Object cellVal) {
        return Collections.emptyList();
    }

    @Override
    public List<XSSFCell> generateXlsxCell(XSSFWorkbook workbook, XSSFSheet sheet, XSSFRow row, Object cellVal) {
        return Collections.emptyList();
    }

    @Override
    public Map<String, Object> getValueFromXlsCell(Map<String, HSSFCell> cells) {
        return Collections.emptyMap();
    }

    @Override
    public Map<String, Object> getValueFromXlsxCell(Map<String, XSSFCell> cells) {
        return Collections.emptyMap();
    }



    public void buildCascadedXls(HSSFWorkbook workbook, HSSFSheet sheet) {
        Set<CascadedOption> rootDataOption = this.cascadedOptions.keySet();
        //根数据
        String[] rootDataArr = new String[rootDataOption.size()];
        int indexOption = 0;
        for (CascadedOption cascadedOption : rootDataOption) {
            rootDataArr[indexOption] = cascadedOption.option();
            indexOption++;
        }
        //创建级联的sheet页
        String cascadedSheetName = "CascadedOptions_" + this.field.getFieldName();
        HSSFSheet cascadedSheet = workbook.createSheet(cascadedSheetName);
        //填充级联sheet页
        int rowIndex = 0;
        //填充父级
        if (rootDataArr.length < 255) {
            //如果父级数据量小于255
            HSSFRow rootOptionsRow = cascadedSheet.createRow(rowIndex++);
            rootOptionsRow.createCell(0).setCellValue("根数据");
            for (int i = 0; i < rootDataArr.length; i++) {
                Cell row2 = rootOptionsRow.createCell(i + 1);
                row2.setCellValue(rootDataArr[i]);
            }
        } else {
            //如果父级数据量大于于255
            HSSFRow rootOptionsRow = cascadedSheet.createRow(rowIndex);
            rootOptionsRow.createCell(0).setCellValue("根数据");
            for (int i = 0; i < rootDataArr.length; i++) {
                HSSFCell cell = rootOptionsRow.createCell(i % 254 + 1);
                cell.setCellValue(rootDataArr[i]);
                if (i > 0 && i % 254 == 0) {
                    rowIndex++;
                    rootOptionsRow = cascadedSheet.createRow(rowIndex);
                }
            }
        }
        //填充子级
        for (CascadedOption rootOption : this.cascadedOptions.keySet()) {
            String rootOptionVal = rootOption.option();
            List<CascadedOption> childOptions = this.cascadedOptions.get(rootOption);
            HSSFName name = workbook.createName();
            StringBuilder formulaStr = new StringBuilder();
            String[] childOptionsArr = new String[childOptions.size()];
            for (int i = 0; i < childOptions.size(); i++) {
                childOptionsArr[i] = childOptions.get(i).option();
            }
            if (childOptionsArr.length < 255) {
                //如果子级的数量小于255
                HSSFRow optionsSheetRow = cascadedSheet.createRow(rowIndex++);
                optionsSheetRow.createCell(0).setCellValue(rootOptionVal);
                for (int i = 0; i < childOptionsArr.length; i++) {
                    Cell cell0 = optionsSheetRow.createCell(i + 1);
                    cell0.setCellValue(childOptionsArr[i]);
                }
                //添加名称管理器
                String range = getRange(1, rowIndex, childOptionsArr.length);
                String nName = rootOptionVal.replace("（", "_").replace("(", "_");
                String lName = nName.replace("）", "_").replace(")", "_");
                name.setNameName(lName);
                String formula = cascadedSheetName + "!" + range;
                formulaStr.append(formula);
            } else {
                int count = childOptionsArr.length % 254 == 0 ? childOptionsArr.length / 254 : childOptionsArr.length / 254 + 1;
                int max = 0;
                int index = 0;
                int startRow = rowIndex + 1;
                for (int i = 0; i < count; i++) {
                    HSSFRow optionsSheetRow = cascadedSheet.createRow(rowIndex++);
                    if (i == 0) {
                        optionsSheetRow.createCell(0).setCellValue(rootOptionVal);
                    }
                    for (int j = 0; j < 255; j++) {
                        if (index == childOptionsArr.length) {
                            break;
                        }
                        HSSFCell cell = optionsSheetRow.createCell(j + 1);
                        cell.setCellValue(childOptionsArr[index]);
                        index++;
                        max++;
                    }
                }
                //添加名称管理器
                String range = getRange(1, startRow, startRow + count - 1, 255);
                String nName = rootOptionVal.replace("（", "_").replace("(", "_");
                String lName = nName.replace("）", "_").replace(")", "_");
                name.setNameName(lName);
                String formula = cascadedSheetName + "!" + range;
                formulaStr.append(formula);
            }
            name.setRefersToFormula(formulaStr.toString());
        }
        for (CascadedItem columnCascadedItem : this.columnCascadedItems) {
            int lookupIndex = columnCascadedItem.getLookupIndex();
            int cascadedItemIndex = columnCascadedItem.getIndex();
            if (lookupIndex == -1) {
                setSelectOptionsXls(workbook, sheet, rootDataArr , cascadedItemIndex, cascadedItemIndex);
            } else {
                setXlsDataValidationListCol(sheet, lookupIndex, cascadedItemIndex, cascadedItemIndex);
            }
        }
    }

    private void buildCascadedXlsx(XSSFWorkbook workbook, XSSFSheet sheet) {
    }


    /**
     * 设置获取范围函数（<255）
     * @param offset        偏移量
     * @param rowIndex      行号
     * @param colCount      列数
     * @return  函数体
     */
    private String getRange(int offset, int rowIndex, int colCount) {
        char start = (char) ('A' + offset);
        if (colCount <= 25) {
            char end = (char) (start + colCount - 1);
            return "$" + start + "$" + rowIndex + ":$" + end + "$" + rowIndex;
        } else {
            char endPrefix = 'A';
            char endSuffix = 'A';
            if ((colCount - 25) / 26 == 0 || colCount == 51) {
                if ((colCount - 25) % 26 == 0) {// 杈圭???
                    endSuffix = (char) ('A' + 25);
                } else {
                    endSuffix = (char) ('A' + (colCount - 25) % 26 - 1);
                }
            } else {// 51浠ヤ?
                if ((colCount - 25) % 26 == 0) {
                    endSuffix = (char) ('A' + 25);
                    endPrefix = (char) (endPrefix + (colCount - 25) / 26 - 1);
                } else {
                    endSuffix = (char) ('A' + (colCount - 25) % 26 - 1);
                    endPrefix = (char) (endPrefix + (colCount - 25) / 26);
                }
            }
            return "$" + start + "$" + rowIndex + ":$" + endPrefix + endSuffix + "$" + rowIndex;
        }
    }
    /**
     * 设置获取范围函数（>255）
     * @param offset 偏移量
     * @param startRow 开始行
     * @param endRow 结束行
     * @return 函数体
     */
    private String getRange(int offset, int startRow, int endRow, int maxColCount) {
        char start = (char) ('A' + offset);
        if (maxColCount <= 25) {
            char end = (char) (start + maxColCount - 1);
            return "$" + start + "$" + startRow + ":$" + end + "$" + endRow;
        } else {
            char endPrefix = 'A';
            char endSuffix = 'A';
            if ((maxColCount - 25) / 26 == 0 || maxColCount == 51) {
                if ((maxColCount - 25) % 26 == 0) {// 杈圭???
                    endSuffix = (char) ('A' + 25);
                } else {
                    endSuffix = (char) ('A' + (maxColCount - 25) % 26 - 1);
                }
            } else {// 51浠ヤ?
                if ((maxColCount - 25) % 26 == 0) {
                    endSuffix = (char) ('A' + 25);
                    endPrefix = (char) (endPrefix + (maxColCount - 25) / 26 - 1);
                } else {
                    endSuffix = (char) ('A' + (maxColCount - 25) % 26 - 1);
                    endPrefix = (char) (endPrefix + (maxColCount - 25) / 26);
                }
            }
            return "$" + start + "$" + startRow + ":$" + endPrefix + endSuffix + "$" + endRow;
        }
    }


    private void setXlsDataValidationListCol(HSSFSheet sheet, int lookUpColIndex,
                                             int firstCol, int endCol) {
        HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper(sheet);
        String offset = getColNameByColIndex(lookUpColIndex);

        for (int i = 1; i <= QExcel.MAX_ROW_ELS; i++) {
            HSSFDataValidation dataValidationByFormula = getXlsDataValidationByFormula(dvHelper,
                    "INDIRECT($" + offset + "$" + (i) + ")", i - 1, i - 1, firstCol, endCol);
            sheet.addValidationData(dataValidationByFormula);
        }
    }

    /**
     * 根据列号得到列的名
     * @param colIndex 位置
     * @return  列名
     */
    private String getColNameByColIndex(int colIndex) {
        StringBuilder result = new StringBuilder();
        colIndex+=1;
        while (colIndex > 0) {
            colIndex--; // 减一以适应从1开始的 Excel 列号
            char digit = (char) (colIndex % 26 + 'A');
            result.insert(0, digit);
            colIndex /= 26;
        }
        return result.toString();
    }

    private  HSSFDataValidation getXlsDataValidationByFormula(HSSFDataValidationHelper dataValidationHelper, String formulaString, int firstRow, int endRow,
                                                              int firstCol, int endCol) {
        //加载下拉列表内容
        //举例：若formulaString = "INDIRECT($A$2)" 表示规则数据会从名称管理器中获取key与单元格 A2 值相同的数据，
        DataValidationConstraint dvConstraint = dataValidationHelper.createFormulaListConstraint(formulaString);
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
        HSSFDataValidation validation = (HSSFDataValidation) dataValidationHelper.createValidation(dvConstraint, regions);
        validation.setEmptyCellAllowed(true);
        validation.setSuppressDropDownArrow(false);
        validation.setShowErrorBox(false);
        // 设置输入信息提示信息
        //validation.createPromptBox("选择错误提示", "你输入的值未在备选列表中，请下拉选择合适的值！");
        validation.createErrorBox("选择错误提示", "你输入的值未在备选列表中，请下拉选择合适的值！");
        return validation;
    }


    /**
     * 设置下拉选项
     *
     * @param workbook 工作簿
     * @param sheet    sheet
     * @param options  选项
     * @param firstCol 起始列
     * @param endCol   结束列
     */
    private void setSelectOptionsXls(HSSFWorkbook workbook, HSSFSheet sheet, String[] options, int firstCol,
                                     int endCol) {
        CellRangeAddressList regions = new CellRangeAddressList(1, QExcel.MAX_ROW_ELS, firstCol, endCol);
        DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
        DataValidationConstraint createExplicitListConstraint = dataValidationHelper.createExplicitListConstraint(options);
        DataValidation createValidation = dataValidationHelper.createValidation(createExplicitListConstraint, regions);
        createValidation.setSuppressDropDownArrow(false);
        try {
            sheet.addValidationData(createValidation);
        } catch (Exception ignore) {
            DataValidation dataValidationList255Col = getXlsDataValidationList255Col(workbook, sheet, options, firstCol, endCol, firstCol);
            sheet.addValidationData(dataValidationList255Col);
        }
    }

    /**
     * 当下拉选项过长时，使用该方法（xlsx文件没有该问题）
     *
     * @param workbook    文件
     * @param sheet       sheet页
     * @param options     下拉选项
     * @param firstCol    第一列
     * @param endCol      最后列
     * @param columnIndex 列位置
     * @return 下拉框
     */
    private DataValidation getXlsDataValidationList255Col(HSSFWorkbook workbook, HSSFSheet sheet, String[] options,
                                                          int firstCol, int endCol, int columnIndex) {
        Sheet hidden = workbook.createSheet("hidden_" + columnIndex);
        Cell cell = null;
        for (int i = 0, length = options.length; i < length; i++)
        {
            String name = options[i];
            Row row = hidden.createRow(i);
            cell = row.createCell(0);
            cell.setCellValue(name);
        }

        Name namedCell = workbook.createName();
        namedCell.setNameName("hidden_" + columnIndex);
        namedCell.setRefersToFormula("hidden_" + columnIndex + "!$A$1:$A$" + options.length);
        //加载数据,将名称为hidden的
        DVConstraint constraint = DVConstraint.createFormulaListConstraint("hidden_" + columnIndex);

        // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList addressList = new CellRangeAddressList(1, QExcel.MAX_ROW_ELS, firstCol,endCol);

        return new HSSFDataValidation(addressList, constraint);
    }


}
